Imports System.Data.OleDb
Imports DTO

Public Class KhachHang_Dao

    Inherits AbstractDAO

    Public Sub New()

    End Sub

    Public Function LayBang() As DataTable

        Dim dt As New DataTable()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From KhachHang"
        ' B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, conn)
        da.FillSchema(dt, SchemaType.Source)
        da.Fill(dt)
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return dt

    End Function
    Public Function LayDanhSach() As IList

        Dim ds As New ArrayList()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From KhachHang"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim khDto As New KhachHang_Dto
            khDto.MaKH = dr("MaKH")
            khDto.TenKH = dr("TenKH")
            khDto.CMND = dr("CMND")
            khDto.SoDT = dr("SoDT")
            khDto.DiaChi = dr("DiaChi")
            ds.Add(khDto)
        End While
        ' B5: Dong ket noi CSDL
        dr.Close()
        conn.Close()
        Return ds

    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From KhachHang"
        'B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, conn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub
    Public Function TimKiem(ByVal mkh As Integer) As KhachHang_Dto

        Dim khDto As New KhachHang_Dto
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From KhachHang Where MaKH = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaKH", OleDbType.Integer)
        cmd.Parameters("@MaKH").Value = mkh
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            khDto = New KhachHang_Dto()
            khDto.MaKH = dr("MaKH")
            khDto.TenKH = dr("TenKH")
            khDto.CMND = dr("CMND")
            khDto.SoDT = dr("SoDT")
            khDto.DiaChi = dr("DiaChi")
        End While
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return khDto

    End Function

    Public Sub Them(ByVal khDto As KhachHang_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Insert into KhachHang(TenKH, CMND,SoDT, DiaChi) values (?, ?, ?, ?)"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)

        cmd.Parameters.Add("@TenKH", OleDbType.WChar)
        cmd.Parameters.Add("@CMND", OleDbType.WChar)
        cmd.Parameters.Add("@SoDT", OleDbType.WChar)
        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)

        cmd.Parameters("@TenKH").Value = khDto.TenKH
        cmd.Parameters("@CMND").Value = khDto.CMND
        cmd.Parameters("@SoDT").Value = khDto.SoDT
        cmd.Parameters("@DiaChi").Value = khDto.DiaChi

        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, conn)
        khDto.MaKH = CInt(cmd.ExecuteScalar())
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Xoa(ByVal mkh As Integer)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Delete From KhachHang Where MaKH = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaKH", OleDbType.Integer)
        cmd.Parameters("@MaKH").Value = mkh
        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Sua(ByVal khDto As KhachHang_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Update KhachHang Set TenKH= ? , CMND = ? ,SoDT=? , DiaChi = ? Where MaKH = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@TenKH", OleDbType.WChar)
        cmd.Parameters.Add("@CMND", OleDbType.WChar)
        cmd.Parameters.Add("@SoDT", OleDbType.WChar)
        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters.Add("@MaKH", OleDbType.Integer)

        cmd.Parameters("@TenKH").Value = khDto.TenKH
        cmd.Parameters("@CMND").Value = khDto.CMND
        cmd.Parameters("@DiaChi").Value = khDto.DiaChi
        cmd.Parameters("@SoDT").Value = khDto.SoDT
        cmd.Parameters("@MaKH").Value = khDto.MaKH

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub


    Public Function TraCuuKH(ByVal khCrt As KhachHang_Crt) As DataTable

        Dim dt As New DataTable()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3 & 4: Tao chuoi strSQL thao tac CSDL & thuc thi chuoi strSQL
        Dim cmd As OleDbCommand = BuildQuery(khCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        ' B5: Dong ket noi CSDL
        cn.Close()
        Return dt

    End Function

    Private Function BuildQuery(ByVal khCrt As KhachHang_Crt, ByVal cn As OleDbConnection) As OleDbCommand

        Dim cmd As New OleDbCommand()
        Dim strSQL As String
        Dim strSelect As String
        Dim strFrom As String
        Dim strWhere As String

        Dim strDKTenKH = " 1 = 1 "
        Dim strDKCMND = " 1 = 1 "
        Dim strDKSoDT = " 1 = 1 "
        Dim strDKDiaChi = " 1 = 1 "
        Dim strDKNgayThue = " 1 = 1 "
        Dim strDKSoPhong = " 1 = 1 "

        strSelect = "Select distinct kh.MaKH, kh.TenKH, kh.CMND, kh.SoDT, kh.DiaChi "
        strFrom = "From KhachHang kh ,PhieuThuePhong pt, Phong p"
        ' neu chon ten hoac CMND hoac DT hoac DC hoac NGay THue Hoac Phong THi them 
        ' Neu chon ca 
        If khCrt.CheckTenKH OrElse khCrt.CheckCMND OrElse khCrt.CheckSoDT OrElse khCrt.CheckSoPhong OrElse khCrt.CheckNgayThue Then
            strSelect += " , p.SoPhong, pt.NgayThue "
        End If
        'Tra cuu theo ten
        If khCrt.CheckTenKH And khCrt.TenKH <> "" Then
            'strSelect += " , p.SoPhong, pt.NgayThue "
            strDKTenKH = " kh.TenKH like ? and kh.MaKH=pt.MaKH and p.MaPHG=pt.MaPHG "
            cmd.Parameters.Add("@TenKH", OleDbType.WChar)
            cmd.Parameters("@TenKH").Value = "%" + khCrt.TenKH + "%"
        End If
        ' Chon tra cuu  CMND/Passport 
        If khCrt.CheckCMND And khCrt.CMND <> "" Then
            'strSelect += " , p.SoPhong, pt.NgayThue "
            strDKCMND = " kh.CMND like ? and kh.MaKH=pt.MaKH and p.MaPHG=pt.MaPHG "
            cmd.Parameters.Add("@CMND", OleDbType.WChar)
            cmd.Parameters("@CMND").Value = "%" + khCrt.CMND + "%"
        End If

        ' Chon tra cuu theo so dien thoai
        If khCrt.CheckSoDT And khCrt.SoDT <> "" Then
            'strSelect += " , p.SoPhong, pt.NgayThue "
            strDKSoDT = " kh.SoDT like ? and kh.MaKH=pt.MaKH and p.MaPHG=pt.MaPHG "
            cmd.Parameters.Add("@SoDT", OleDbType.WChar)
            cmd.Parameters("@SoDT").Value = "%" + khCrt.SoDT + "%"
        End If

        ' Tra cuu theo dia chi khach    
        If khCrt.CheckDiaChi And khCrt.DiaChi <> "" Then
            strDKDiaChi = " kh.DiaChi like ? and kh.MaKH=pt.MaKH and p.MaPHG=pt.MaPHG "
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%" + khCrt.DiaChi + "%"
        End If

        ' Tra cuu theo ngay thue
        If khCrt.CheckNgayThue Then
            strDKNgayThue = "pt.MaKH=kh.MaKH and p.MaPHG=pt.MaPHG and pt.NgayThue between ? and ?"
            cmd.Parameters.Add("@NgayThueTu", OleDbType.Date)
            cmd.Parameters.Add("@NgayThueDen", OleDbType.Date)
            cmd.Parameters("@NgayThueTu").Value = khCrt.NgayThueTu
            cmd.Parameters("@NgayThueDen").Value = khCrt.NgayThueDen

        End If
        ' Tra cuu theo so phong
        If khCrt.CheckSoPhong And khCrt.SoPhong <> 0 Then
            strDKSoPhong = "p.SoPhong = ? and kh.MaKH=pt.MaKH and p.MaPHG = pt.MaPHG"
            cmd.Parameters.Add("@SoPhong", OleDbType.Integer)
            cmd.Parameters("@SoPhong").Value = khCrt.SoPhong
        End If
        strWhere = " Where " + strDKTenKH
        strWhere += " and " + strDKCMND
        strWhere += " and " + strDKSoDT
        strWhere += " and " + strDKDiaChi
        strWhere += " and " + strDKNgayThue
        strWhere += " and " + strDKSoPhong
        strSQL = strSelect + strFrom + strWhere
        strSQL += " Order by kh.MaKH"

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd

    End Function
End Class
